﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KitBuildingWF.Models;
using Advantage.Data;
using Advantage.Data.Provider;
using System.Collections;

namespace KitBuildingWF.DataAccess
{
    /// <summary>
    /// DAO for Stock class
    /// </summary>
    public class StockNativeDAO : GenericNativeDAO<Stock>, StockDAO
    {
        AdsConnection conn;
        AdsCommand cmd;
        AdsDataReader adsReader;
        int iField;
        Dictionary<String, String> stockPropertiesMap = new Dictionary<String, String>();

        /// <summary>
        /// Stock Native DAO constructer
        /// Set the stock property list
        /// </summary>
        public StockNativeDAO()
        {
            // Mappings between db column name => Stock properties
            stockPropertiesMap.Add("NAME", "productCode");
            stockPropertiesMap.Add("NUMBER", "StockID");
            stockPropertiesMap.Add("STOCK_IN", "StockIn");
            stockPropertiesMap.Add("BACKORDER", "BackOrder");
            stockPropertiesMap.Add("TITLE", "Description");
            stockPropertiesMap.Add("GROUP", "Group");
            stockPropertiesMap.Add("LOCATION", "Location");
        }

        /// <summary>
        /// find the Stock item by id:
        /// </summary>
        /// <param name="Id">exactly 10 character</param>
        /// <returns>the stock item</returns>
        public Stock findById(string Id)
        {
            conn = GetConnection();
            conn.Open();
            cmd = conn.CreateCommand();
            Dictionary<String, String> stockItemProperties = new Dictionary<string,string>();

            string cmdStatement = "select * from Stock where Number = '" + Id + "'";
            cmd.CommandText = cmdStatement;
            //cmd.CommandText = "select * from Massupma";

            adsReader = cmd.ExecuteReader();

            while (adsReader.Read())
            {
                for (iField = 0; iField < adsReader.FieldCount; iField++)
                {
                    if (stockPropertiesMap.ContainsKey(adsReader.GetName(iField)))
                    {
                        stockItemProperties.Add(stockPropertiesMap[adsReader.GetName(iField)], adsReader.GetValue(iField).ToString());
                    }
                    // Console.WriteLine(adsReader.GetName(iField));

                }
            }

            conn.Close();

            if (stockItemProperties.Count > 0)
                return new Stock(stockItemProperties);
            else
                return null;
        }

        /// <summary>
        /// find all stock items
        /// </summary>
        /// <returns></returns>
        public List<Stock> findAll()
        {
            List<Stock> stockList = new List<Stock>();
           // Stock stock = new Stock();
            conn = GetConnection();
            conn.Open();
            cmd = conn.CreateCommand();
            Dictionary<String, String> stockItemProperties = new Dictionary<string, string>();

            string cmdStatement = "select * from Stock";
            cmd.CommandText = cmdStatement;
            //cmd.CommandText = "select * from Massupma";

            adsReader = cmd.ExecuteReader();

            while (adsReader.Read())
            {
                // clear the previous stock item properties
                stockItemProperties.Clear();

                // retreive properties of the current stock item
                for (iField = 0; iField < adsReader.FieldCount; iField++)
                {
                    if (stockPropertiesMap.ContainsKey(adsReader.GetName(iField)))
                    {
                        stockItemProperties.Add(stockPropertiesMap[adsReader.GetName(iField)], adsReader.GetValue(iField).ToString());
                    }
                }

                // Construct and add the stock item into the list
                if (stockItemProperties.Count > 0)
                    stockList.Add(new Stock(stockItemProperties));
            }

            conn.Close();


            return stockList;
        }
    }
}
